package weaver.aiyh_pcn.workflow_to_customer.config;

import aiyh.utils.zwl.common.ToolUtil;
import org.h2.util.StringUtils;
import weaver.aiyh_pcn.workflow_to_customer.model.MainResult;
import weaver.aiyh_pcn.workflow_to_customer.model.TableFieldRelation;
import weaver.aiyh_pcn.workflow_to_customer.utils.Utils;
import weaver.conn.RecordSet;
import weaver.general.Util;

import java.util.*;
import java.util.stream.Collectors;

/**
 * 客户信息字段与流程字段映射配置
 */
public class CRM_Config extends ToolUtil {
	private final String className = "CRM_Config";
	private final String workflowId;
	private final String mainTable;
	private final String requestId;
	private MainResult mainResult = null;
	private final Map<String, List<Map<String,Object>>> configResult;


	public CRM_Config(String workflowId, String mainTable, String requestId) {
		this.workflowId = workflowId;
		this.mainTable = mainTable;
		this.requestId = requestId;
		this.configResult = new HashMap<>();
	}

	/**
	 * 获取客户信息字段映射配置
	 *
	 */
	public Map<String, List<Map<String,Object>>> getConfigInfo() {
		RecordSet rs = new RecordSet();
		Map<String, Set<String>> configMap = new HashMap<>();
		List<TableFieldRelation> configList = new ArrayList<>();

//       查询配置表基本信息
		String selectConfigBase = "select lf.id, lf.is_out_user,lf.is_sell_change,lf.will_write, " +
				"wfvw.fieldname as will_write_field,wfvw.tablename as will_write_table, " +
				"lf.is_customer_address,lf.workflow_type, lf.customer_id, " +
				"lf.change_setting, wslu.selectvalue as update_mark, " +
				"lf.manager_type, lf.customer_manager, wfvm.fieldname as manager_field, " +
				"wsli.selectvalue as insert_mark,wfv.fieldname as update_insert, " +
				"wfv.tablename as update_insert_table " +
				"from uf_external_law_fir as lf " +
				"left join workflow_selectitem as wslu on wslu.id = lf.update_mark " +
				"left join workflow_selectitem as wsli on wsli.id = lf.insert_mark " +
				"left join workflow_field_view as wfv on wfv.id = lf.update_insert " +
				"left join workflow_field_view as wfvm on wfvm.id = lf.manager_field " +
				"left join workflow_field_view as wfvw on wfvw.id = lf.will_write_field " +
				"where workflow_type in " +
				"(select id from workflow_base where activeVersionID in " +
				"(select activeVersionID  from workflow_base where id = ?) or id = ?)";
		rs.executeQuery(selectConfigBase, this.workflowId, this.workflowId);
		while (rs.next()) {
			int mainId = Util.getIntValue(rs.getInt("id"));
//			流程类型
			int workflowType = Util.getIntValue(rs.getString("workflow_type"));
//			是否创建联系人
			int isOutUser = Util.getIntValue(rs.getString("is_out_user"));
//			是否创建销售商机
			int isSellChange = Util.getIntValue(rs.getString("is_sell_change"));
//			是否创建客户地址
			int isCustomerAddress = Util.getIntValue(rs.getString("is_customer_address"));
//			流转设置
			int changeSetting = Util.getIntValue(rs.getInt("change_setting"));
//			更新客户信息对应主键字段
			String customerId = Util.null2String(rs.getString("customer_id"));
//			更新和插入标识
			String updateInsert = Util.null2String(rs.getString("update_insert"));
//			更新标识值
			int updateMark = Util.getIntValue(rs.getString("update_mark"),-1);
//			插入标识值
			int insertMark = Util.getIntValue(rs.getString("insert_mark"),-1);
//          客户经理类型
			int managerType = Util.getIntValue(rs.getString("manager_type"), -1);
			//客户经理id
			int customerManager = Util.getIntValue(rs.getString("customer_manager"),0);
			//客户经理在流程中的字段
			String managerField = Util.null2String(rs.getString("manager_field"));
			String updateInsertTable = Util.null2String(rs.getString("update_insert_table"));
//			是否回写到流程
			int willWrite = Util.getIntValue(rs.getString("will_write"),0);
//			会写到流程的某个字段
			String willWriteField = Util.null2String(rs.getString("will_write_field"));
			String willWriteTable = Util.null2String(rs.getString("will_write_table"));
//			对查询数据进行一个实体类封装
			this.mainResult = MainResult.builder()
					.mainId(mainId)
					.workflowType(workflowType)
					.isOutUser(isOutUser)
					.isSellChange(isSellChange)
					.isCustomerAddress(isCustomerAddress)
					.customerId(customerId)
					.updateInsert(updateInsert)
					.updateMark(updateMark)
					.insertMark(insertMark)
					.changeSetting(changeSetting)
					.managerType(managerType)
					.customerManager(customerManager)
					.managerField(managerField.toLowerCase())
					.updateInsertTable(updateInsertTable)
					.willWrite(willWrite)
					.willWriteField(willWriteField.toLowerCase())
					.willWriteTable(willWriteTable)
					.build();
		}

		this.writeDebuggerLog(mainResult.toString());
		this.writeDebuggerLog("\n===============================================\n");

//		如果该条流程没有配置信息，则表示不用执行流转客户操作
		if (this.mainResult == null) {
			return null;
		}

//        查询配置表明细表
		String selectConfigData = "select lfd.id,lfd.change_rules, cv.fieldname as customer_field," +
				"lfd.rules_value,wv.fieldname as workflow_field, wv.tablename as workflow_table, " +
				"cv.tablename as customer_table,lfd.customer_type,lfd.out_user_field " +
				"from uf_external_law_fir_dt1 as lfd " +
				"left join workflow_field_view as wv on wv.id = lfd.workflow_field " +
				"left join customer_view as cv on cv.id = lfd.customer_field " +
				"where lfd.mainid = ?";
		rs.executeQuery(selectConfigData, this.mainResult.getMainId());

		while (rs.next()) {
//			明细表id
			int id = Util.getIntValue(rs.getInt("id"));
//			转换规则
			int changeRules = Util.getIntValue(rs.getString("change_rules"));
//			客户模块类型
			int customerType = Util.getIntValue(rs.getString("customer_type"));
//			转换规则值
			String rulesValue = Util.null2String(rs.getString("rules_value"));
//			流程字段
			String workflowField = Util.null2String(rs.getString("workflow_field"));
//			客户模块字段
			String customerField = Util.null2String(rs.getString("customer_field"));
//			流程字段对应的流程表
			String workflowTable = Util.null2String(rs.getString("workflow_table"));
//			客户模块字段对应的客户模块表
			String customerTable = Util.null2String(rs.getString("customer_table"));
//			外部用户字段
			String outUserField = Util.null2String(rs.getString("out_user_field"));
			TableFieldRelation tableFieldRelation =
					TableFieldRelation.builder()
							.id(id)
							.changeRules(changeRules)
							.customerType(customerType)
							.rulesValue(rulesValue)
							.workflowField(workflowField.toLowerCase())
							.customerField(customerField.toLowerCase())
							.outUserField(outUserField.toLowerCase())
							.workflowTable(workflowTable)
							.customerTable(customerTable).build();
			configList.add(tableFieldRelation);
			if (!configMap.containsKey(customerTable)) {
				if (customerType == 1) {
					configMap.put("hrmResource", new HashSet<String>() {{
						add(workflowTable);
					}});
				} else {
					configMap.put(customerTable, new HashSet<String>() {{
						add(workflowTable);
					}});
				}
			} else {
				if (customerType == 1) {
					configMap.get("hrmResource").add(workflowTable);
				} else {
					configMap.get(customerTable).add(workflowTable);
				}
			}
		}

		this.writeDebuggerLog("configList： "+configList.toString());
		this.writeDebuggerLog("configList： "+configMap.toString());
//		获取客户基本信息
		this.configResult.put("CRM_CustomerInfo",this.getCRM_CustomerInfoSqlMapList(configList,configMap));
		this.writeDebuggerLog("configList： "+this.configResult.toString());
//		判断是否需要创建外部用户
		if (this.mainResult.getIsOutUser() == 0) {
//			需要创建外部用户,获取外部用户字段映射信息
			this.configResult.put("CRM_CustomerContacter",this.getCRM_CustomerContacterSqlMapList(configList,configMap));
			this.configResult.put("hrmResource",this.getHrmResourceSqlMapList(configList,configMap));
		}else if(this.mainResult.getIsOutUser() == 1){
//	        不需要创建外部用户，但是需要创建联系人,获取联系人字段映射信息
			this.configResult.put("CRM_CustomerContacter",this.getCRM_CustomerContacterSqlMapList(configList,configMap));
		}

//		判断是否需要创建商机
		if(this.mainResult.getIsSellChange() == 0){
//			需要创建商机，获取上级字段映射信息
			this.configResult.put("CRM_SellChance",this.getCRM_SellChanceSqlMapList(configList,configMap));
		}

//	    判断是否需要创建客户地址
		if(this.mainResult.getIsCustomerAddress() == 0){
//			需要创建客户地址，获取客户地址字段映射信息
			this.configResult.put("CRM_CustomerAddress",this.getCRM_CustomerAddressSqlMapList(configList,configMap));
		}

		return this.configResult;
	}

	public List<Map<String, Object>> getHrmResourceSqlMapList(List<TableFieldRelation> configList,Map<String, Set<String>> configMap) {
		List<TableFieldRelation> hrmResourceList = configList
				.stream().filter(item -> item.getCustomerType() == 1)
				.collect(Collectors.toList());
		List<Map<String, Object>> hrmResourceSqlMapList = this.getSqlMapList(hrmResourceList, configMap.get("hrmResource"));
		return hrmResourceSqlMapList;
	}

	public List<Map<String,Object>> getCRM_CustomerInfoSqlMapList(List<TableFieldRelation> configList,Map<String, Set<String>> configMap) {
		List<TableFieldRelation>  CRM_CustomerInfoList = configList
				.stream().filter(item -> "CRM_CustomerInfo".equalsIgnoreCase(item.getCustomerTable()))
				.collect(Collectors.toList());
		List<Map<String,Object>> CRM_CustomerInfoSqlMapList = this.getSqlMapList(CRM_CustomerInfoList, configMap.get("CRM_CustomerInfo"));
		return CRM_CustomerInfoSqlMapList;
	}

	public List<Map<String, Object>> getCRM_CustomerContacterSqlMapList(List<TableFieldRelation> configList,Map<String, Set<String>> configMap) {
		List<TableFieldRelation>  CRM_CustomerContacterList = configList
				.stream().filter(item -> "CRM_CustomerContacter".equalsIgnoreCase(item.getCustomerTable()))
				.collect(Collectors.toList());
		List<Map<String, Object>>  CRM_CustomerContacterSqlMapList = this.getSqlMapList(CRM_CustomerContacterList, configMap.get("CRM_CustomerContacter"));
		return CRM_CustomerContacterSqlMapList;
	}

	public List<Map<String, Object>> getCRM_CustomerAddressSqlMapList(List<TableFieldRelation> configList,Map<String, Set<String>> configMap) {
		List<TableFieldRelation> CRM_CustomerAddressList = configList
				.stream().filter(item -> "CRM_CustomerAddress".equalsIgnoreCase(item.getCustomerTable()))
				.collect(Collectors.toList());
		List<Map<String, Object>> CRM_CustomerAddressSqlMapList = this.getSqlMapList(CRM_CustomerAddressList, configMap.get("CRM_CustomerAddress"));
		return CRM_CustomerAddressSqlMapList;
	}

	public List<Map<String, Object>> getCRM_SellChanceSqlMapList(List<TableFieldRelation> configList,Map<String, Set<String>> configMap) {
		List<TableFieldRelation>  CRM_SellChanceList = configList
				.stream().filter(item -> "CRM_SellChance".equalsIgnoreCase(item.getCustomerTable()))
				.collect(Collectors.toList());
		List<Map<String, Object>> CRM_SellChanceSqlMapList = this.getSqlMapList(CRM_SellChanceList, configMap.get("CRM_SellChance"));
		return CRM_SellChanceSqlMapList;
	}
	
	public List<Map<String,Object>> getSqlMapList(List<TableFieldRelation> configList, Set<String> set){
		this.writeDebuggerLog("getSqlMapList: configList： " + configList.toString());
		this.writeDebuggerLog("getSqlMapList: set： " + set.toString());
		if(Objects.isNull(configList) || configList.size() == 0 || Objects.isNull(set) || set.size() == 0){
			this.writeDebuggerLog("table mapping error,error args is configList = [" + configList + "] and set = [" + set + "]");
			return null;
		}
		RecordSet rs = new RecordSet();
		List<Map<String,Object>> sqlMapList = new ArrayList<>();
		for (String tableName : set) {
//			过滤表数据
			List<TableFieldRelation> tableList = configList
					.stream().filter(item -> tableName.equals(item.getWorkflowTable()))
					.collect(Collectors.toList());
			String selectSql;
//			判断属于那个表
			if("main_table".equals(tableName)){
//				表示数据来源于主表,通过表名查询数据信息
				selectSql = "select * from " + this.mainTable + " where requestid = ?";
			}else{
//				属于明细表
				selectSql = "select * from " + tableName + " where mainid in " +
						"(select id from " + this.mainTable + " where requestid = ?)";
			}
			if(rs.executeQuery(selectSql, this.requestId)){
//				获取查询所有数据
				while(rs.next()){
					Map<String,Object> sqlMap = new HashMap<>();
//					循环获取字段和值的映射关系
					tableList.forEach(tableField -> {
						String value = "";
						switch (tableField.getChangeRules()){
							case 0:
//								转换规则为不转换
								value = Util.null2String(rs.getString(tableField.getWorkflowField()));
								break;
							case 1:
//								转换规则为固定值
								value = tableField.getRulesValue();
								break;
							case 2:
//								转换规则为自定义SQL
								String customSql = tableField.getRulesValue();
								if (customSql.contains("delete") || customSql.contains("update") ||
										customSql.contains("exec") || customSql.contains("drop") ||
										customSql.contains("truncate")) {
//					                sql 不包含delete，update，exec，drop，truncate等危险关键字
									writeDebuggerLog("warning sql: " + customSql);
								} else if (customSql.contains("select")) {
//					                包含查询关键词
									try {
										rs.executeQuery(customSql);
									} catch (Exception e) {
										writeErrorLog("execute custom sql error: " + customSql);
										writeErrorLog("error info:" + e.getMessage() + "\n");
									}
									rs.next();
									value = Util.null2String(rs.getString(1));
								}
								break;
							case 3:
//								转换规则为requestId
								value = this.requestId;
								break;
							case 6:
//								转换规则是系统日期
								String format = getSystemParamValue("ExternalLawFirmsFormatDate");
								if(StringUtils.isNullOrEmpty(format)){
									format = "yyyy-MM-dd";
								}
								try{
									value = Utils.getTime(format);
								}catch(Exception e){
									writeErrorLog(className,"format date error, format string is: " + format);
									writeErrorLog("error info:" + e.getMessage() + "\n");
								}
								break;
							case 7:
//								转换规则是系统日期时间
								String formatTime = getSystemParamValue("ExternalLawFirmsFormatDateTime");
								if(StringUtils.isNullOrEmpty(formatTime)){
									formatTime = "yyyy-MM-dd HH:mm:ss";
								}
								try{
									value = Utils.getTime(formatTime);
								}catch(Exception e){
									writeErrorLog(className,"format dateTime error, format string is: " + formatTime);
									writeErrorLog("error info:" + e.getMessage() + "\n");
								}
								break;
							default:
								break;
						}
						if(tableField.getCustomerType() == 0){
							sqlMap.put(tableField.getCustomerField().toLowerCase()
									, value);
						}else{
//							 bug,外部用户的字段不能出现在客户基本信息里面
							sqlMap.put(tableField.getOutUserField().toLowerCase()
									, value);
						}
					});
					this.writeDebuggerLog("SqlMap:" + sqlMap);
					sqlMapList.add(sqlMap);
				}
			}else{
				writeDebuggerLog("select workflow date error, error sql is: "
						+ selectSql + " args is: " + this.workflowId);
			}
		}
		return sqlMapList;
	}


	public MainResult getMainResult() {
		return mainResult;
	}
}
